﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using System.Data.SqlClient;


namespace RLZY.others
{
    class SJ
    {
        /// <summary>
        /// cal
        /// </summary>
        public int cal(String bm,string  date)
        {
           
            int rowsAffected = 0;
           
            int i;
            IDataParameter[] iData = new SqlParameter[2];
            iData[0] = new SqlParameter("@date", date.Substring(0,7));
            iData[1] = new SqlParameter("@name", bm);

            IDataParameter[] iDatabb = new SqlParameter[1];
            iDatabb[0] = new SqlParameter("@date", date.Substring(0, 7));
            
            if (bm == "特钢本部")
            {
                i = DbHelperSQL.RunProcedure("pro_SJTJ_TGBB", iDatabb, out rowsAffected);
            }
            else { i = DbHelperSQL.RunProcedure("pro_SJTJ_ALL", iData, out rowsAffected); }
            
           
            return i;

        }
        /// <summary>
        /// 四金合并显示
        /// </summary>
        public int calHB(String bm, string date)
        {

            int rowsAffected = 0;

            int i;
            IDataParameter[] iData = new SqlParameter[2];
            iData[0] = new SqlParameter("@date", date.Substring(0, 7));
            iData[1] = new SqlParameter("@name", bm);

            IDataParameter[] iDatabb = new SqlParameter[1];
            iDatabb[0] = new SqlParameter("@date", date.Substring(0, 7));

            if (bm == "特钢本部")
            {
                i = DbHelperSQL.RunProcedure("pro_SJHB_TGBB", iDatabb, out rowsAffected);
            }
            else { i = DbHelperSQL.RunProcedure("pro_SJHB_ALL", iData, out rowsAffected); }


            return i;

        }
        /// <summary>
        /// 新余公积金表
        /// </summary>
        public int calGJJ(String bm, string date)
        {

            int rowsAffected = 0;

            int i;
            IDataParameter[] iData = new SqlParameter[2];
            iData[0] = new SqlParameter("@date", date.Substring(0, 7));
            iData[1] = new SqlParameter("@name", bm);

            IDataParameter[] iDatabb = new SqlParameter[1];
            iDatabb[0] = new SqlParameter("@date", date.Substring(0, 7));

            if (bm == "特钢本部")
            {
                i = DbHelperSQL.RunProcedure("pro_XYGJJ_TGBB", iDatabb, out rowsAffected);
            }
            else { i = DbHelperSQL.RunProcedure("pro_XYGJJ_ALL", iData, out rowsAffected); }


            return i;

        }
        /// <summary>
        /// 新余公积金表
        /// </summary>
        public DataSet GetGJJ()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select 人数 , 个人公积金10 from GZ1  where 用工性质 ='合计'  ");
            return DbHelperSQL.Query(strSql.ToString(), null);


           

        }
        /// <summary>
        /// 删除GZ1表
        /// </summary>
        public int DropGZ1()
        {
            List<String> list = new List<string>();
            list.Add(" if  exists (select name from sysobjects where name='GZ1')  drop table GZ1 ");
            list.Add("  if  exists (select name from sysobjects where name='TmpGZ')  drop table TmpGZ ");

            return DbHelperSQL.ExecuteSqlTran(list);
        }
        /// <summary>
        /// 四金分类显示cal
        /// </summary>
        public DataSet GetListsijinfenlei()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,用工性质,人数 , 个人失业金1, 企业失业金2 ,合计失业金 ,个人养老金8 ,企业养老金20,合计养老金, 个人医疗金2 ,企业医疗金7,合计医疗金,个人公积金10,企业公积金10, 合计公积金,工伤保险 , 总计 from GZ1 order by ID asc  ");


            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 四金合并显示cal
        /// </summary>
        public DataSet GetListHB()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,用工性质,人数 , 个人失业金1, 企业失业金2 ,合计失业金 ,个人养老金8 ,企业养老金20,合计养老金, 个人医疗金2 ,企业医疗金7,合计医疗金,个人公积金10,企业公积金10, 合计公积金,工伤保险 , 总计 from GZ1 order by ID asc    ");


            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 总养老金
        /// </summary>
        public DataSet GetSumYLJ(String BM, string date)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (BM == "特钢本部")
            {
                strSql.Append(" sum(人数) as 人数,sum(扣养老金) as 扣养老金   from GZ   where (部门单位='特钢机关' or 部门单位='型钢厂'  or  部门单位='冷带厂' or  部门单位='新安特' or  部门单位='工贸')    and substring(日期,1,7)=@date  and  扣养老金 <>0   ");
            }
            else {
                strSql.Append("  sum(人数) as 人数,sum(扣养老金) as 扣养老金   from GZ   where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='')   and substring(日期,1,7)=@date  and  扣养老金 <>0 ");
            }
           

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",BM),
                new SqlParameter("@date",date),

            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }
        /// <summary>
        /// 总失业保险
        /// </summary>
        public DataSet GetSumSYBX(String BM, string date)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (BM == "特钢本部")
            {
                strSql.Append(" sum(人数) as 人数,sum(扣失业保险) as 扣失业保险    from GZ   where (部门单位='特钢机关' or 部门单位='型钢厂'  or  部门单位='冷带厂' or  部门单位='新安特' or  部门单位='工贸')    and substring(日期,1,7)=@date  and  扣失业保险 <>0   ");
            }
            else
            {
                strSql.Append("  sum(人数) as 人数,sum(扣失业保险) as 扣失业保险   from GZ   where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='')   and substring(日期,1,7)=@date  and  扣失业保险 <>0 ");
            }


            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",BM),
                new SqlParameter("@date",date),

            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }
        /// <summary>
        /// 总公积金
        /// </summary>
        public DataSet GetSumGJJ(String BM, string date)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (BM == "特钢本部")
            {
                strSql.Append(" sum(人数) as 人数,sum(扣公积金) as 扣公积金     from GZ   where (部门单位='特钢机关' or 部门单位='型钢厂'  or  部门单位='冷带厂' or  部门单位='新安特' or  部门单位='工贸')    and substring(日期,1,7)=@date  and  扣公积金 <>0   ");
            }
            else
            {
                strSql.Append("  sum(人数) as 人数,sum(扣公积金) as 扣公积金    from GZ   where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='')   and substring(日期,1,7)=@date  and  扣公积金 <>0 ");
            }


            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",BM),
                new SqlParameter("@date",date),

            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }
        /// <summary>
        /// 扣医疗保险
        /// </summary>
        public DataSet GetSumYLBX(String BM, string date)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (BM == "特钢本部")
            {
                strSql.Append(" sum(人数) as 人数,sum(扣医疗保险) as 扣医疗保险      from GZ   where (部门单位='特钢机关' or 部门单位='型钢厂'  or  部门单位='冷带厂' or  部门单位='新安特' or  部门单位='工贸')    and substring(日期,1,7)=@date  and  扣医疗保险 <>0   ");
            }
            else
            {
                strSql.Append("  sum(人数) as 人数,sum(扣医疗保险) as 扣医疗保险     from GZ   where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='')   and substring(日期,1,7)=@date  and  扣医疗保险 <>0 ");
            }


            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",BM),
                new SqlParameter("@date",date),

            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }
        /// <summary>
        /// 人数
        /// </summary>
        public DataSet GetSumRS(String BM, string date)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (BM == "特钢本部")
            {
                strSql.Append(" sum(人数) as 人数       from GZ   where (部门单位='特钢机关' or 部门单位='型钢厂'  or  部门单位='冷带厂' or  部门单位='新安特' or  部门单位='工贸')    and substring(日期,1,7)=@date     ");
            }
            else
            {
                strSql.Append("  sum(人数) as 人数      from GZ   where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='')   and substring(日期,1,7)=@date   ");
            }


            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",BM),
                new SqlParameter("@date",date),

            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }
        /// <summary>
        /// 显示测奖金空表
        /// </summary>
        public int cjjkb(string startdate, string enddate)
        {

            int rowsAffected = 0;

            int i;
            IDataParameter[] iData = new SqlParameter[2];
            iData[0] = new SqlParameter("@startdate", startdate);
            iData[1] = new SqlParameter("@enddate", enddate);
            i = DbHelperSQL.RunProcedure("pro_CJJKB", iData, out rowsAffected);
            return i;

        }
        /// <summary>
        /// 人数
        /// </summary>
        public DataSet Getcjjkb()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  * from GZ1    order by 部门单位");
            
            return DbHelperSQL.Query(strSql.ToString(), null);

        }

    }
}
